package com.tss.utils;

import java.sql.*;

/**
 * 数据库连接工具类
 */
public class DBUtil {
    private static final String DB_URL = "jdbc:sqlite:tss.db";
    private static Connection connection;

    // 私有构造函数，防止实例化
    private DBUtil() {}

    /**
     * 获取数据库连接
     * @return 数据库连接对象
     */
    public static Connection getConnection() {
        try {
            if (connection == null || connection.isClosed()) {
                // 加载SQLite JDBC驱动
                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection(DB_URL);
            }
            return connection;
        } catch (SQLException | ClassNotFoundException e) {
            System.err.println("数据库连接错误: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭资源
     */
    public static void closeResources(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化数据库表结构和基础数据
     */
    public static void initDatabase() {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = getConnection();
            stmt = conn.createStatement();

            // 创建目的地表
            stmt.execute("CREATE TABLE IF NOT EXISTS destination (" +
                    "destination_code TEXT PRIMARY KEY," +
                    "destination_name TEXT NOT NULL" +
                    ")");

            // 创建车票类型表
            stmt.execute("CREATE TABLE IF NOT EXISTS ticket_type (" +
                    "ticket_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "ticket_type TEXT NOT NULL," +
                    "category TEXT NOT NULL," +
                    "price REAL NOT NULL," +
                    "validity_days INTEGER NOT NULL" +
                    ")");

            // 创建交易记录表
            stmt.execute("CREATE TABLE IF NOT EXISTS transaction_record (" +
                    "transaction_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "dest_code TEXT NOT NULL," +
                    "ticket_type TEXT NOT NULL," +
                    "amount REAL NOT NULL," +
                    "payment_method TEXT NOT NULL," +
                    "time TEXT NOT NULL," +
                    "is_cancelled INTEGER DEFAULT 0" +
                    ")");

            // 插入一些示例数据
            // 检查目的地表是否有数据
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM destination");
            rs.next();
            int count = rs.getInt(1);
            
            if (count == 0) {
                // 插入目的地数据
                stmt.execute("INSERT INTO destination VALUES ('20', '总站')");
                stmt.execute("INSERT INTO destination VALUES ('21', '北京站')");
                stmt.execute("INSERT INTO destination VALUES ('22', '上海站')");
                stmt.execute("INSERT INTO destination VALUES ('23', '广州站')");
                stmt.execute("INSERT INTO destination VALUES ('24', '深圳站')");
            }

            // 检查车票类型表是否有数据
            rs = stmt.executeQuery("SELECT COUNT(*) FROM ticket_type");
            rs.next();
            count = rs.getInt(1);
            
            if (count == 0) {
                // 插入车票类型数据
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('单程票', '普通座', 50.0, 1)");
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('单程票', '软座', 80.0, 1)");
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('往返票', '普通座', 90.0, 7)");
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('往返票', '软座', 150.0, 7)");
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('多次票', '普通座', 200.0, 30)");
                stmt.execute("INSERT INTO ticket_type (ticket_type, category, price, validity_days) VALUES ('多次票', '软座', 300.0, 30)");
            }

            System.out.println("数据库初始化成功");

        } catch (SQLException e) {
            System.err.println("初始化数据库错误: " + e.getMessage());
            e.printStackTrace();
        } finally {
            closeResources(conn, null, null);
        }
    }
} 